package cn.cloud.butler.scheduled.repository;

import cn.cloud.butler.scheduled.domain.OperProduceEntity;
import cn.cloud.butler.scheduled.domain.ProduceSumEntitiesResult;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import javax.transaction.Transactional;
import java.util.List;
import java.util.Map;

/**
 * Created by zhouguocheng on 2018-12-17-下午 3:30
 */
public interface OperProduceRepository extends JpaRepository<OperProduceEntity,Integer> {

//    @Query(value = "select distinct MacSN from OperProduce where to_days(UploadTime)=to_days(now())-1",nativeQuery = true)
//    List<String> selectProMac();

    @Query(value = "select DISTINCT aa.MacSN from (select  MacSN from OperProduce where to_days(UploadTime)=to_days(now())-1) as aa",nativeQuery = true)
    List<String> selectProMac();
    @Query(value = "update OperProduce set UploadTime = ?2,PartCount = ?1 where ID = ?1",nativeQuery = true)
    void updateCount(Integer ids, String times, Integer count);

    @Query(value = "select * from OperProduce where MacSN = ?1 and UploadTime = ?2",nativeQuery = true)
    OperProduceEntity findByMacSN(String masn, String time);

    @Query(value = "select ProduceName as producename,COUNT(*) as num from OperProduce where to_days(UploadTime) = TO_DAYS(NOW())-1 AND MacSN = ?1 group by ProduceName",nativeQuery = true)
    List<ProduceSumEntitiesResult> singleProNum(String macSn);

    @Query(value = "  SELECT  " +
            "      o.PartCount as partCount,  " +
            "      o.ProduceName as produceName,  " +
            "      o.MacSN AS macSN,  " +
            "      o.UploadTime as uploadTime,  " +
            "      o.StartTime as StartTime,  " +
            "      o.EndTime as endTime,  " +
            "      o.ProduceTime as ProduceTime,  " +
            "      o.MaxLoad as MaxLoad,  " +
            "      o.Override as Override,  " +
            "      a.MacNumb AS macNumb  " +
            "      FROM  " +
            "      OperProduce o  " +
            "      JOIN CompMacSN c ON c.MacSN = o.MacSN  " +
            "      AND c.MacSN = ?1  " +
            "      JOIN Archives a ON a.MacSN = o.MacSN  " +
            "      where to_days(o.UploadTime) = TO_DAYS(NOW())-1 " +
            "      ORDER BY o.UploadTime asc "
            , nativeQuery = true
    )
    List<Map<String, Object>> findByMacSNToSum(String macSN);

    @Transactional
    @Modifying
    @Query(value = "DELETE FROM `OperProduce` WHERE DATE(`UploadTime`) = ?1 ",nativeQuery = true)
    void deleteDate(String toDay);

    @Query(value = "SELECT * FROM `OperProduce` where UploadTime like %?1%",nativeQuery = true)
    List<OperProduceEntity> findByDay(String toDay);

    @Query(value = "select * FROM `OperProduce` where EndTime is null and UploadTime like %?1%",nativeQuery = true)
    List<OperProduceEntity> findByEndTimeDay(String toDay);
    @Modifying(clearAutomatically = true, flushAutomatically = true)
    @Query(value = "update OperProduce set EndTime = ?4 where MacSN = ?1 and ProduceName = ?2 and UploadTime = ?3",nativeQuery = true)
    void updateOperEndTime(String sn,String name,String startTime,String endTime);
}

